from osv import osv, fields

class kdvn_budget_data(osv.osv):
    _name = "kdvn.budget.data"
    _description="KDVN Budget Data"
    
    def write(self, cr, uid, ids, vals, context=None):
        if ids:
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select id from kdvn_budget_data kbd where id in (%s) and budget_id<>%d  limit 1" % (kbd_ids,vals['budget_id']))
            if cr.fetchall():
                cr.execute("Select \
                            * \
                        from \
                            kdvn_budget_data kbd\
                        left join \
                            product_product pp on kbd.budget_id = kdvn_budget_id\
                        left join \
                            purchase_order_line pol on kbd.project_id = pol.project_id and pp.id = pol.product_id\
                        where coalesce(pol.id,0)>0 and kbd.id in (%s) limit 1" % (kbd_ids))
                if cr.fetchall():
                    raise osv.except_osv("Error","Can not change budget because P.O., Expense is exist!")
        osv.osv.write(self, cr, uid, ids,vals,context=context)
        return True

    def unlink(self, cr, uid, ids, context=None):
        if ids:
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select \
                            * \
                        from \
                            kdvn_budget_data kbd\
                        left join \
                            product_product pp on kbd.budget_id = kdvn_budget_id\
                        left join \
                            purchase_order_line pol on kbd.project_id = pol.project_id and pp.id = pol.product_id\
                        where coalesce(pol.id,0)>0 and kbd.id in (%s) limit 1" % (kbd_ids))
            if cr.fetchall():
                raise osv.except_osv("Error","Can not delete budget(s) because P.O., Expense is exist!")
                unlink_ids = ids
            else:
                unlink_ids = []
        return super(kdvn_budget_data, self).unlink(cr, uid, unlink_ids, context=context)
    
    def _get_budget_data_info1(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            budget_ids=",".join(map(str,ids))
            budget_list_temp={'budget_amount_usd':0.0,
                          'order_vnd':0.0,
                          'order_usd':0.0,   
                          'payment_vnd':0.0,
                          'over':False}
            for id in ids:
                res[id] = budget_list_temp
            cr.execute("Select\
                            kbd.id,\
                            case \
                                when rate =0\
                                then 0 \
                                else coalesce(budget_amount/rate,0) end as budget_amount_usd,\
                            coalesce(subtotalvnd,0) as order_vnd,\
                            coalesce(subtotalusd,0) as order_usd,\
                            coalesce(payment_vnd,0) as payment_vnd,\
                            case when abs(budget_amount)-coalesce(subtotalvnd,0)<0 then true else false end as over\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            kdvn_project kp on kbd.project_id=kp.id\
                        left join\
                            project_currency pc on kp.id=pc.project_id and upper(pc.name)='USD'\
                        left join\
                            (Select \
                                pol.project_id,\
                                kdvn_budget_id as budget_id,\
                                coalesce(sum(price_subtotal2*rate*fnpo_compute(fnpo_currnametoid('VND'),po.currency_id,date_order)),0) as subtotalvnd,\
                                coalesce(sum(price_subtotal2*fnpo_compute(fnpo_currnametoid('USD'),po.currency_id,date_order)),0) as subtotalusd,\
                                sum(payment_vnd*rate) as payment_vnd\
                            from \
                                purchase_order po\
                            left join \
                                purchase_order_line pol on po.id=order_id\
                            left join \
                                vwpo_rate vwr on po.id=vwr.id\
                            left join \
                                product_product pp on product_id=pp.id\
                            left join\
                                (Select\
                                    kropd.project_id,\
                                    budget_id,    \
                                    sum(fnpo_compute(fnpo_currnametoid('VND'),currency_id,rop_date)*(coalesce(kropd.amount,0) + coalesce(kropd.retention_amount,0) + coalesce(kropd.advanced_amount,0))) as payment_vnd\
                                from\
                                    kdvn_request_of_payment krop\
                                left join\
                                    kdvn_request_of_payment_detail kropd on krop.id = kropd.rop_id\
                                where\
                                    krop.state not in ('draft','cancel')\
                                Group by \
                                    kropd.project_id,\
                                    budget_id) vwtemp2 on pol.project_id=vwtemp2.project_id and kdvn_budget_id=vwtemp2.budget_id\
                            where \
                                po.state not in ('draft','cancel')\
                            group by \
                                pol.project_id,kdvn_budget_id) vwtemp1 on kbd.budget_id=vwtemp1.budget_id and kbd.project_id=vwtemp1.project_id\
                        where kbd.id in (%s)" % budget_ids)
            
            for budget_list in cr.dictfetchall():
                res[budget_list.pop('id')] =budget_list        
            return res
        
    def _get_budget_data_info(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            budget_ids=",".join(map(str,ids))
            budget_list_temp={'budget_amount_usd':0.0,
                          'order_vnd':0.0,
                          'order_usd':0.0,   
                          'payment_vnd':0.0,
                          'over':False}
            for id in ids:
                res[id] = budget_list_temp
            cr.execute("Select\
                            kbd.id,\
                            case \
                                when rate =0\
                                then 0 \
                                else coalesce(budget_amount/rate,0) end as budget_amount_usd,\
                            coalesce(subtotalvnd,0) as order_vnd,\
                            coalesce(subtotalusd,0) as order_usd,\
                            coalesce(payment_vnd,0) as payment_vnd,\
                            coalesce(abs(budget_amount)-coalesce(subtotalvnd,0)<0,False) as over\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            kdvn_project kp on kbd.project_id=kp.id\
                        left join\
                            project_currency pc on kp.id=pc.project_id and upper(pc.name)='USD'\
                        left join\
                            (Select \
                                pol.project_id,\
                                kdvn_budget_id as budget_id,\
                                coalesce(sum(price_subtotal2*coalesce(rate,1)*fnpo_compute(fnpo_currnametoid('VND'),po.currency_id,date_order)),0) as subtotalvnd,\
                                coalesce(sum(price_subtotal2*fnpo_compute(fnpo_currnametoid('USD'),po.currency_id,date_order)),0) as subtotalusd\
                            from \
                                purchase_order po\
                            left join \
                                purchase_order_line pol on po.id=order_id\
                            left join \
                                vwpo_rate vwr on po.id=vwr.id\
                            left join \
                                product_product pp on product_id=pp.id\
                            where \
                                po.state not in ('draft','cancel')\
                            group by \
                                pol.project_id,kdvn_budget_id,rate) vwtemp1 on kbd.budget_id=vwtemp1.budget_id and kbd.project_id=vwtemp1.project_id\
                        left join\
                            (Select\
                                    kropd.project_id,\
                                    budget_id,    \
                                    sum(coalesce(rate,1)*fnpo_compute(fnpo_currnametoid('VND'),krop.currency_id,date_order)*(coalesce(kropd.amount,0)\
                                     + coalesce(kropd.retention_amount,0) + coalesce(kropd.advanced_amount,0))) as payment_vnd\
                                from\
                                    kdvn_request_of_payment krop\
                                left join\
                                    purchase_order po on order_id = po.id\
                                left join \
                                    vwpo_rate vwr on po.id=vwr.id\
                                left join\
                                    kdvn_request_of_payment_detail kropd on krop.id = kropd.rop_id\
                                where\
                                    krop.state not in ('draft','cancel') and po.state not in ('draft','cancel') and order_id>0\
                                Group by \
                                    kropd.project_id,\
                                    budget_id) vwtemp_payment on kbd.project_id=vwtemp_payment.project_id and kbd.budget_id=vwtemp_payment.budget_id\
                        where kbd.id in (%s)" % budget_ids)
            
            for budget_list in cr.dictfetchall():
                res[budget_list.pop('id')] =budget_list        
            return res
        
#        cr.execute("Select\
#                            pol.id\
#                        from \
#                            purchase_order pol\
#                        left join \
#                            purchase_order_line po on pol.order_id = po.id\
#                        left join\
#                            kdvn_project kp on pol.project_id=kp.id\
#                        left join\
#                            product_product pp on product_id=pp.id\
#                        where \
#                            PO.state not in ('draft','cancel')\
#                            and pol.project_id="+str(bgm.project_id.id)+" \
#                            and kdvn_budget_id="+str(bgm.budget_id.id)+"\
#                        group by po.id;            
    def _get_orders(self, cr, uid, ids, prop, unknow_none,unknow_dict):
        res={}
        if ids:
            for xid in ids:
                res[xid] = []
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select\
                            kbd.id as kdb_id,\
                            vwpol.id as pol_id\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            (Select \
                                pol.id,kdvn_budget_id,pol.project_id\
                            from\
                                purchase_order_line pol\
                            left join \
                                purchase_order po on pol.order_id = po.id\
                            left join\
                                product_product pp on  product_id=pp.id\
                            where \
                                PO.state not in ('draft','cancel')) vwpol on kbd.project_id=vwpol.project_id and budget_id=vwpol.kdvn_budget_id\
                        where kbd.id in (%s)" % kbd_ids) # po.name similar to kp.code || '%'\
            for kbd_id,pol_id in cr.fetchall():
                if pol_id:
                    res[kbd_id].append(pol_id)
        return res

    def _get_payment(self, cr, uid, ids, prop, unknow_none,unknow_dict):
        res={}
        if ids:
            for xid in ids:
                res[xid] = []
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select\
                            kbd.id as kdb_id,\
                            coalesce(vw_kropd.id) as kropd_id\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            (Select \
                                kropd.id,\
                                kropd.project_id,\
                                kropd.budget_id\
                            from\
                                kdvn_request_of_payment_detail kropd\
                            left join\
                                kdvn_request_of_payment krop on rop_id=krop.id\
                            where\
                                krop.state not in ('draft','cancel')) vw_kropd on kbd.project_id = vw_kropd.project_id and kbd.budget_id = vw_kropd.budget_id\
                        where kbd.id in (%s)" % kbd_ids) # po.name similar to kp.code || '%'\
            for kbd_id,kropd_id in cr.fetchall():
                if kropd_id:
                    res[kbd_id].append(kropd_id)
        return res

    def _get_actual_budget_amount(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            budget_list=",".join(map(str,ids))
            cr.execute("Select \
                            kbd.id,budget_amount*received_rate as actual_budget_amount\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            kdvn_project kp on kbd.project_id=kp.id\
                        left join\
                            (Select\
                                id,\
                                case when sum(plan_cost_vnd+plan_vat_vnd) is null or sum(plan_cost_vnd+plan_vat_vnd)=0 then 1 else\
                                sum(contract_amount_vnd + contract_vat_vnd)/sum(plan_cost_vnd+plan_vat_vnd) end as received_rate\
                            from vwproject_contract_received_summary \
                            group by id) vwtemp on kp.contract_id = vwtemp.id\
                            where kbd.id in (%s)" % budget_list)
            for id,current_vnd in cr.fetchall():
                res[id]=current_vnd
        return res
    
    def _calculate_budget_amountusd(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            acc_set = ",".join(map(str, ids))
            cr.execute("Select \
                            kbd.id,\
                            case when rate =0 \
                            then 0 else coalesce(budget_amount/rate,0) end\
                            as budget_amountusd\
                        from \
                            kdvn_budget_data kbd\
                        left join\
                            kdvn_project kp on kbd.project_id=kp.id\
                        left join\
                            project_currency pc on kp.contract_id=pc.contract_id and upper(pc.name)='USD'\
                        where kbd.id in (%s);" % acc_set)
            for id,amountusd in cr.fetchall():
                res[id]=amountusd
        return res

    def _get_budget_code(self, cr, uid, ids, name, args, context={}):
        res = {}
        if ids:
            for id in ids:
                res[id]=0
            kdb_ids = ",".join(map(str,ids))
            cr.execute("Select \
                            kbd.id,\
                            code\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            account_budget_post abp on kbd.budget_id = abp.id where kbd.id in (%s)" % kdb_ids)
            kdvn_budget_lists = cr.fetchall()
            for kbd_id,code in kdvn_budget_lists:
                    res[kbd_id] = code 
        return res


    _order ="budget_code"
    
    _columns= {
                'budget_code':fields.function(_get_budget_code,type='char',size=10,method=True,string='Budget Code',
                                       store={'kdvn.budget.data': (lambda self, cr, uid, ids, c={}: ids, ['budget_id'], 10)}),
                'budget_id':fields.many2one('account.budget.post','Budget', required=True),
                'project_id':fields.many2one('kdvn.project','Project'),
                'budget_amount':fields.float('Budget Amount', digits=(16,2)),
#                #'actual_budget_amount':fields.function(_get_budget_data_info,
#                                                       type='float',digits=(16,2),
#                                                       string='Current VND',
#                                                       method=True,multi='budget_cal'),
                'budget_amount_usd':fields.function(_get_budget_data_info,
                                                       type='float',digits=(16,2),
                                                       string='USD',
                                                       method=True,multi='budget_cal'),
                'order_vnd':fields.function(_get_budget_data_info,
                                                       type='float',digits=(16,2),
                                                       string='Order VND',
                                                       method=True,multi='budget_cal'),
                'order_usd':fields.function(_get_budget_data_info,
                                                       type='float',digits=(16,2),
                                                       string='USD',
                                                       method=True,multi='budget_cal'),
                'payment_vnd':fields.function(_get_budget_data_info,
                                                       type='float',digits=(16,2),
                                                       string='Payment VND',
                                                       method=True,multi='budget_cal'),
                'over':fields.function(_get_budget_data_info,
                                                       type='boolean',
                                                       string='Over',
                                                       method=True,multi='budget_cal'),
                'order_ids':fields.function(_get_orders,type='one2many',relation='purchase.order.line',method=True,string='PO',link=0),
                'payment_detail_ids':fields.function(_get_payment,type='one2many',relation='kdvn.request.of.payment.detail',method=True,string='Payment',link=0),
                
#                'actual_budget_amount':fields.function(_get_actual_budget_amount,
#                                                       type='float',digits=(16,2),
#                                                       method=True,string='Current VND'),
#                'budget_amount_usd':fields.function(_calculate_budget_amountusd,type='float',method=True,string="USD")                          
                }
    _sql_constraints = [('code_unique_budget_project', 'unique (budget_id,project_id)', 'KDVN Error: The Budget of this project must be unique !')] 
    _defaults = {  
        'budget_amount':lambda *x:0.0,
        }    
kdvn_budget_data()


class kdvn_total_budget_cat(osv.osv):
    _name = 'kdvn.total.budget.cat'
    _description='KDVN Total Budget Check Input'
    _auto=False
    _columns={
         "material":fields.float("Material"),
         "sub_contractor":fields.float('Sub-Contractor'),
         "site_expense":fields.float("Site-Expense"),
         "kinden_salary_admin_cost":fields.float("Salary, Admin. Cost"),
         "bussiness_profit":fields.float("Business Profit"),
         "total":fields.float("Total")
        }
    def init(self,cr):
        cr.execute("Create or replace view kdvn_total_budget_cat as \
                        Select \
                            id,\
                            sum(material) as material,\
                            sum(sub_contractor) as sub_contractor,\
                            sum(site_expense) as site_expense,\
                            sum(kinden_salary_admin_cost) as kinden_salary_admin_cost,\
                            sum(bussiness_profit) as bussiness_profit,\
                            sum(material)+sum(site_expense)+sum(sub_contractor)+sum(kinden_salary_admin_cost)+sum(bussiness_profit) as total\
                        from\
                            (Select\
                                project_id as id,\
                                sum(coalesce(budget_amount,0)) as material,\
                                0 as sub_contractor,\
                                0 as site_expense,\
                                0 as kinden_salary_admin_cost,\
                                0 as bussiness_profit\
                            from \
                                kdvn_budget_data kbd\
                            left join\
                                kdvn_project kp on kbd.project_id=kp.id\
                            left join\
                                account_budget_post abp on kbd.budget_id = abp.id\
                            left join\
                                budget_category bc on abp.categ_id = bc.id\
                            where\
                                cat_code='material'\
                            group by cat_code,project_id\
                            union\
                            Select \
                                project_id as id,\
                                0 as material,\
                                sum(coalesce(budget_amount,0)) as sub_contractor,\
                                0 as site_expense,\
                                0 as kinden_salary_admin_cost,\
                                0 as bussiness_profit    \
                            from \
                                kdvn_budget_data kbd\
                            left join\
                                kdvn_project kp on kbd.project_id=kp.id\
                            left join\
                                account_budget_post abp on kbd.budget_id = abp.id\
                            left join\
                                budget_category bc on abp.categ_id = bc.id\
                            where\
                                cat_code='sub_contractor'\
                            group by cat_code,project_id\
                            union\
                            Select \
                                project_id as id,\
                                0 as material,\
                                0 as sub_contractor,\
                                sum(coalesce(budget_amount,0)) as site_expense,\
                                0 as kinden_salary_admin_cost,\
                                0 as bussiness_profit    \
                            from \
                                kdvn_budget_data kbd\
                            left join\
                                kdvn_project kp on kbd.project_id=kp.id\
                            left join\
                                account_budget_post abp on kbd.budget_id = abp.id\
                            left join\
                                budget_category bc on abp.categ_id = bc.id\
                            where\
                                cat_code='site_expense'\
                            group by cat_code,project_id\
                            union\
                            Select \
                                project_id as id,\
                                0 as material,\
                                0 as sub_contractor,\
                                0 as site_expense,\
                                sum(coalesce(budget_amount,0)) as kinden_salary_admin_cost,\
                                0 as bussiness_profit    \
                            from \
                                kdvn_budget_data kbd\
                            left join\
                                kdvn_project kp on kbd.project_id=kp.id\
                            left join\
                                account_budget_post abp on kbd.budget_id = abp.id\
                            left join\
                                budget_category bc on abp.categ_id = bc.id\
                            where\
                                cat_code='kinden_salary_admin_cost'\
                            group by cat_code,project_id\
                            union\
                            Select \
                                project_id as id,\
                                0 as material,\
                                0 as sub_contractor,\
                                0 as site_expense,\
                                0 as kinden_salary_admin_cost,\
                                sum(coalesce(budget_amount,0)) as bussiness_profit    \
                            from \
                                kdvn_budget_data kbd\
                            left join\
                                kdvn_project kp on kbd.project_id=kp.id\
                            left join\
                                account_budget_post abp on kbd.budget_id = abp.id\
                            left join\
                                budget_category bc on abp.categ_id = bc.id\
                            where\
                                cat_code='bussiness_profit'\
                            group by cat_code,project_id) vwtemp group by id")        
kdvn_total_budget_cat()

class kdvn_project(osv.osv):
    _name = "kdvn.project"
    _inherit = "kdvn.project"
  
    _columns={
             'budget_total_cat_ids':fields.one2many('kdvn.total.budget.cat','id','Budget Category Total'),
             }
kdvn_project()